﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Maticsoft.DBUtility;
using System.Data.SqlClient;

namespace ExtendBLL
{
    public class PersonalProject:LabMS.BLL.PersonalProject
    {
        public void DeleteApplicantByProjectID(int ProjectID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete PersonalProjApp where PersonalProjID=" + ProjectID);
            try
            {
                DbHelperSQL.ExecuteSql(strSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DeleteConsumeByProjectID(int ProjectID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete PersonalProjConsume where PersonalProjID=" + ProjectID);
            try
            {
                DbHelperSQL.ExecuteSql(strSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UpdateStatus(string strNewStatus, int ProjectID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update PersonalProject set ProjectStatus ='" + strNewStatus + "' where ProjectID = " + ProjectID);
            try
            {
                DbHelperSQL.ExecuteSql(strSql.ToString());
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        //通过学生序号获取学生信息
        public List<ExtendModule.StudentAndOther> GetStudentInfoListByID(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select s.*,p.Pro_Name as Pro_Name, c.Class_Name as Class_Name from student s left join professional p on s.Student_Pro = p.id ");
            strSql.Append("left join class c on s.ClassID=c.id ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = new DataSet();
            ds = DbHelperSQL.Query(strSql.ToString());

            List<ExtendModule.StudentAndOther> modelList = new List<ExtendModule.StudentAndOther>();
            int rowsCount = ds.Tables[0].Rows.Count;
            if (rowsCount > 0)
            {
                ExtendModule.StudentAndOther model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new ExtendModule.StudentAndOther();
                    if (ds.Tables[0].Rows[n]["ID"].ToString() != "")
                    {
                        model.ID = int.Parse(ds.Tables[0].Rows[n]["ID"].ToString());
                    }
                    model.Student_Code = ds.Tables[0].Rows[n]["Student_Code"].ToString();
                    model.Student_Name = ds.Tables[0].Rows[n]["Student_Name"].ToString();
                    model.Student_Pass = ds.Tables[0].Rows[n]["Student_Pass"].ToString();
                    model.Student_Summary = ds.Tables[0].Rows[n]["Student_Summary"].ToString();
                    if (ds.Tables[0].Rows[n]["ClassID"].ToString() != "")
                    {
                        model.ClassID = int.Parse(ds.Tables[0].Rows[n]["ClassID"].ToString());
                    }
                    model.Student_Sex = ds.Tables[0].Rows[n]["Student_Sex"].ToString();
                    if (ds.Tables[0].Rows[n]["Student_Pro"].ToString() != "")
                    {
                        model.Student_Pro = int.Parse(ds.Tables[0].Rows[n]["Student_Pro"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["Student_EntryDate"].ToString() != "")
                    {
                        model.Student_EntryDate = DateTime.Parse(ds.Tables[0].Rows[n]["Student_EntryDate"].ToString());
                    }
                    model.Student_Grade = ds.Tables[0].Rows[n]["Student_Grade"].ToString();
                    if (ds.Tables[0].Rows[n]["Student_Photo"].ToString() != "")
                    {
                        model.Student_Photo = (byte[])ds.Tables[0].Rows[n]["Student_Photo"];
                    }
                    model.Pro_Name = ds.Tables[0].Rows[n]["Pro_Name"].ToString();
                    model.Class_Name = ds.Tables[0].Rows[n]["Class_Name"].ToString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        //通过申请人学号获取学生信息
        public List<ExtendModule.StudentAndOther> GetStudentInfoList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select s.*,p.Pro_Name as Pro_Name, c.Class_Name as Class_Name from student s left join professional p on s.Student_Pro = p.id ");
            strSql.Append("left join class c on s.ClassID=c.id left join PersonalProjApp pa on s.id = pa.ApplicantID ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" where " + strWhere);
            }
            DataSet ds = new DataSet();
            ds = DbHelperSQL.Query(strSql.ToString());

            List<ExtendModule.StudentAndOther> modelList = new List<ExtendModule.StudentAndOther>();
            int rowsCount = ds.Tables[0].Rows.Count;
            if (rowsCount > 0)
            {
                ExtendModule.StudentAndOther model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new ExtendModule.StudentAndOther();
                    if (ds.Tables[0].Rows[n]["ID"].ToString() != "")
                    {
                        model.ID = int.Parse(ds.Tables[0].Rows[n]["ID"].ToString());
                    }
                    model.Student_Code = ds.Tables[0].Rows[n]["Student_Code"].ToString();
                    model.Student_Name = ds.Tables[0].Rows[n]["Student_Name"].ToString();
                    model.Student_Pass = ds.Tables[0].Rows[n]["Student_Pass"].ToString();
                    model.Student_Summary = ds.Tables[0].Rows[n]["Student_Summary"].ToString();
                    if (ds.Tables[0].Rows[n]["ClassID"].ToString() != "")
                    {
                        model.ClassID = int.Parse(ds.Tables[0].Rows[n]["ClassID"].ToString());
                    }
                    model.Student_Sex = ds.Tables[0].Rows[n]["Student_Sex"].ToString();
                    if (ds.Tables[0].Rows[n]["Student_Pro"].ToString() != "")
                    {
                        model.Student_Pro = int.Parse(ds.Tables[0].Rows[n]["Student_Pro"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["Student_EntryDate"].ToString() != "")
                    {
                        model.Student_EntryDate = DateTime.Parse(ds.Tables[0].Rows[n]["Student_EntryDate"].ToString());
                    }
                    model.Student_Grade = ds.Tables[0].Rows[n]["Student_Grade"].ToString();
                    if (ds.Tables[0].Rows[n]["Student_Photo"].ToString() != "")
                    {
                        model.Student_Photo = (byte[])ds.Tables[0].Rows[n]["Student_Photo"];
                    }
                    model.Pro_Name = ds.Tables[0].Rows[n]["Pro_Name"].ToString();
                    model.Class_Name = ds.Tables[0].Rows[n]["Class_Name"].ToString();  
                    modelList.Add(model);
                }
            }
            return modelList;       
        }


        public List<ExtendModule.PersonalProjectAndOther> PersonalProjectList(string strAnd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select PersonalProject.*,d.[Name] as ProjectStatusName, Lab_Name as LabName, Location_Name as LocationName ");
            strSql.Append("from PersonalProject left join dictionary d on ProjectStatus = d.Code and d.DClass='perprojectstatus' ");
            strSql.Append("left join Lab on AppcationLabID = Lab.id left join  Location on ProjectLabID = Location.id ");
            strSql.Append("where 1=1");

            if (!string.IsNullOrEmpty(strAnd))
            {
                strSql.Append(strAnd);
            }

            DataSet ds = new DataSet();
            ds = DbHelperSQL.Query(strSql.ToString());

            List<ExtendModule.PersonalProjectAndOther> modelList = new List<ExtendModule.PersonalProjectAndOther>();

            int rowsCount = ds.Tables[0].Rows.Count;
            if (rowsCount > 0)
            {
                ExtendModule.PersonalProjectAndOther model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new ExtendModule.PersonalProjectAndOther();
                    if (ds.Tables[0].Rows[n]["ProjectID"].ToString() != "")
                    {
                        model.ProjectID = int.Parse(ds.Tables[0].Rows[n]["ProjectID"].ToString());
                    }
                    model.ProjectName = ds.Tables[0].Rows[n]["ProjectName"].ToString();
                    if (ds.Tables[0].Rows[n]["PlanHours"].ToString() != "")
                    {
                        model.PlanHours = int.Parse(ds.Tables[0].Rows[n]["PlanHours"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["AppcationLabID"].ToString() != "")
                    {
                        model.AppcationLabID = int.Parse(ds.Tables[0].Rows[n]["AppcationLabID"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ApplicationStartTime"].ToString() != "")
                    {
                        model.ApplicationStartTime = DateTime.Parse(ds.Tables[0].Rows[n]["ApplicationStartTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ApplicationEndTime"].ToString() != "")
                    {
                        model.ApplicationEndTime = DateTime.Parse(ds.Tables[0].Rows[n]["ApplicationEndTime"].ToString());
                    }
                    model.Teacher = ds.Tables[0].Rows[n]["Teacher"].ToString();
                    model.TeacherTitle = ds.Tables[0].Rows[n]["TeacherTitle"].ToString();
                    model.TeacherPhone = ds.Tables[0].Rows[n]["TeacherPhone"].ToString();
                    model.TeacherEmail = ds.Tables[0].Rows[n]["TeacherEmail"].ToString();
                    model.Contactor = ds.Tables[0].Rows[n]["Contactor"].ToString();
                    model.ContactorClass = ds.Tables[0].Rows[n]["ContactorClass"].ToString();
                    model.ContactorPhone = ds.Tables[0].Rows[n]["ContactorPhone"].ToString();
                    model.ContactorEmail = ds.Tables[0].Rows[n]["ContactorEmail"].ToString();
                    model.ApplicationReason = ds.Tables[0].Rows[n]["ApplicationReason"].ToString();
                    model.ProjectStatus = ds.Tables[0].Rows[n]["ProjectStatus"].ToString();
                    model.Approval = ds.Tables[0].Rows[n]["Approval"].ToString();
                    model.Opinion = ds.Tables[0].Rows[n]["Opinion"].ToString();
                    if (ds.Tables[0].Rows[n]["ProjectStartTime"].ToString() != "")
                    {
                        model.ProjectStartTime = DateTime.Parse(ds.Tables[0].Rows[n]["ProjectStartTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ProjectEndTime"].ToString() != "")
                    {
                        model.ProjectEndTime = DateTime.Parse(ds.Tables[0].Rows[n]["ProjectEndTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ProjectLabID"].ToString() != "")
                    {
                        model.ProjectLabID = int.Parse(ds.Tables[0].Rows[n]["ProjectLabID"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["SYear"].ToString() != "")
                    {
                        model.SYear = int.Parse(ds.Tables[0].Rows[n]["SYear"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["Semester"].ToString() != "")
                    {
                        model.Semester = int.Parse(ds.Tables[0].Rows[n]["Semester"].ToString());
                    }
                    model.ProjectStatusName = ds.Tables[0].Rows[n]["ProjectStatusName"].ToString();
                    model.LabName = ds.Tables[0].Rows[n]["LabName"].ToString();
                    model.LocationName = ds.Tables[0].Rows[n]["LocationName"].ToString();
                    model.ApplicationTime = Convert.ToDateTime(model.ApplicationStartTime).ToShortDateString() + " — " + Convert.ToDateTime(model.ApplicationEndTime).ToShortDateString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }

        public List<ExtendModule.PersonalProjectAndOther> GetPersonalProjectListByUserCode(string strAnd)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select a.* from Student ,(select PersonalProjApp.*, PersonalProject.*,d.[Name] as ProjectStatusName, Lab_Name as LabName, Location_Name as LocationName ");
            strSql.Append("from PersonalProject left join dictionary d on ProjectStatus = d.Code and d.DClass='perprojectstatus' ");
            strSql.Append("left join Lab on AppcationLabID = Lab.id left join  Location on ProjectLabID = Location.id ");
            strSql.Append("left join PersonalProjApp on PersonalProject.ProjectID=PersonalProjApp.PersonalProjID) as a ");
            strSql.Append("where 1=1");

            if (!string.IsNullOrEmpty(strAnd))
            {
                strSql.Append(strAnd);
            }

            DataSet ds = new DataSet();
            ds = DbHelperSQL.Query(strSql.ToString());

            List<ExtendModule.PersonalProjectAndOther> modelList = new List<ExtendModule.PersonalProjectAndOther>();

            int rowsCount = ds.Tables[0].Rows.Count;
            if (rowsCount > 0)
            {
                ExtendModule.PersonalProjectAndOther model;
                for (int n = 0; n < rowsCount; n++)
                {
                    model = new ExtendModule.PersonalProjectAndOther();
                    if (ds.Tables[0].Rows[n]["ProjectID"].ToString() != "")
                    {
                        model.ProjectID = int.Parse(ds.Tables[0].Rows[n]["ProjectID"].ToString());
                    }
                    model.ProjectName = ds.Tables[0].Rows[n]["ProjectName"].ToString();
                    if (ds.Tables[0].Rows[n]["PlanHours"].ToString() != "")
                    {
                        model.PlanHours = int.Parse(ds.Tables[0].Rows[n]["PlanHours"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["AppcationLabID"].ToString() != "")
                    {
                        model.AppcationLabID = int.Parse(ds.Tables[0].Rows[n]["AppcationLabID"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ApplicationStartTime"].ToString() != "")
                    {
                        model.ApplicationStartTime = DateTime.Parse(ds.Tables[0].Rows[n]["ApplicationStartTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ApplicationEndTime"].ToString() != "")
                    {
                        model.ApplicationEndTime = DateTime.Parse(ds.Tables[0].Rows[n]["ApplicationEndTime"].ToString());
                    }
                    model.Teacher = ds.Tables[0].Rows[n]["Teacher"].ToString();
                    model.TeacherTitle = ds.Tables[0].Rows[n]["TeacherTitle"].ToString();
                    model.TeacherPhone = ds.Tables[0].Rows[n]["TeacherPhone"].ToString();
                    model.TeacherEmail = ds.Tables[0].Rows[n]["TeacherEmail"].ToString();
                    model.Contactor = ds.Tables[0].Rows[n]["Contactor"].ToString();
                    model.ContactorClass = ds.Tables[0].Rows[n]["ContactorClass"].ToString();
                    model.ContactorPhone = ds.Tables[0].Rows[n]["ContactorPhone"].ToString();
                    model.ContactorEmail = ds.Tables[0].Rows[n]["ContactorEmail"].ToString();
                    model.ApplicationReason = ds.Tables[0].Rows[n]["ApplicationReason"].ToString();
                    model.ProjectStatus = ds.Tables[0].Rows[n]["ProjectStatus"].ToString();
                    model.Approval = ds.Tables[0].Rows[n]["Approval"].ToString();
                    model.Opinion = ds.Tables[0].Rows[n]["Opinion"].ToString();
                    if (ds.Tables[0].Rows[n]["ProjectStartTime"].ToString() != "")
                    {
                        model.ProjectStartTime = DateTime.Parse(ds.Tables[0].Rows[n]["ProjectStartTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ProjectEndTime"].ToString() != "")
                    {
                        model.ProjectEndTime = DateTime.Parse(ds.Tables[0].Rows[n]["ProjectEndTime"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["ProjectLabID"].ToString() != "")
                    {
                        model.ProjectLabID = int.Parse(ds.Tables[0].Rows[n]["ProjectLabID"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["SYear"].ToString() != "")
                    {
                        model.SYear = int.Parse(ds.Tables[0].Rows[n]["SYear"].ToString());
                    }
                    if (ds.Tables[0].Rows[n]["Semester"].ToString() != "")
                    {
                        model.Semester = int.Parse(ds.Tables[0].Rows[n]["Semester"].ToString());
                    }
                    model.ProjectStatusName = ds.Tables[0].Rows[n]["ProjectStatusName"].ToString();
                    model.LabName = ds.Tables[0].Rows[n]["LabName"].ToString();
                    model.LocationName = ds.Tables[0].Rows[n]["LocationName"].ToString();
                    model.ApplicationTime = Convert.ToDateTime(model.ApplicationStartTime).ToShortDateString() + " — " + Convert.ToDateTime(model.ApplicationEndTime).ToShortDateString();
                    modelList.Add(model);
                }
            }
            return modelList;
        }
    }
}
